-- @owner: danny20001210
-- @date: 2025-5-15
-- @testpoint:验证不同对象类型使用xmltype模块正确，success
@conn PrimaryDbAmode;
drop table if exists table_1266741 cascade;
drop view if exists view1_1266741;
drop MATERIALIZED view if exists incre_mater_view1_1266741;
drop MATERIALIZED view if exists mater_view1_1266741;
drop table if exists as_1_1266741 cascade;
drop table if exists like_1_1266741 cascade;
drop table if exists sc_1_1266741 cascade;
drop synonym if exists sy_1_1266741 cascade;


create table table_1266741( ID int, name clob, data xmltype );
Insert INTO table_1266741 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;
--普通视图
create view view1_1266741 as select * from table_1266741;
--全量物化视图
create MATERIALIZED view mater_view1_1266741 as select * from table_1266741;
--增量物化视图
create INCREMENTAL MATERIALIZED view incre_mater_view1_1266741 as select * from table_1266741;
--create table as
create table as_1_1266741 as select * from table_1266741;
--create table like
create table like_1_1266741 (like table_1266741 including all);
--同义词
create synonym sy_1_1266741 for table_1266741;
--select into
select * into sc_1_1266741 from table_1266741;
select extract(x.data,'/collection/record/datafield/subfield') xmlseq from view1_1266741 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from view1_1266741 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from view1_1266741 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from view1_1266741 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from view1_1266741 x;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from mater_view1_1266741 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from mater_view1_1266741 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from mater_view1_1266741 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from mater_view1_1266741 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from mater_view1_1266741 x;


select extract(x.data,'/collection/record/datafield/subfield') xmlseq from incre_mater_view1_1266741 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from incre_mater_view1_1266741 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from incre_mater_view1_1266741 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from incre_mater_view1_1266741 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from incre_mater_view1_1266741 x;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from as_1_1266741 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from as_1_1266741 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from as_1_1266741 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from as_1_1266741 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from as_1_1266741 x;

Insert INTO like_1_1266741 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from like_1_1266741 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from like_1_1266741 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from like_1_1266741 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from like_1_1266741 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from like_1_1266741 x;


select extract(x.data,'/collection/record/datafield/subfield') xmlseq from sc_1_1266741 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from sc_1_1266741 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from sc_1_1266741 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from sc_1_1266741 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from sc_1_1266741 x;



select extract(x.data,'/collection/record/datafield/subfield') xmlseq from sy_1_1266741 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from sy_1_1266741 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from sy_1_1266741 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from sy_1_1266741 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from sy_1_1266741 x;

drop table if exists table_1266741 cascade;
drop view if exists view1_1266741;
drop MATERIALIZED view if exists incre_mater_view1_1266741;
drop MATERIALIZED view if exists mater_view1_1266741;
drop table if exists as_1_1266741 cascade;
drop table if exists like_1_1266741 cascade;
drop table if exists sc_1_1266741 cascade;
drop synonym if exists sy_1_1266741 cascade;

